import psycopg2
import os
import re 


def select_table(cargs,tile):
    query = f"""
        SELECT distinct dt_sy.qhmc
        FROM china_tile
        JOIN dt_sy 
        on st_intersects(china_tile.geom,dt_sy.shape)
        WHERE china_tile.tile = '{tile}' 
        """
    connection = psycopg2.connect(**cargs)
    connection.autocommit = True
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        data = cursor.fetchall()
    except Exception as e:
        connection.rollback()
    cursor.close()
    connection.close() 
    return data


def main(sourcedir,targetdir,cargs):
    zipnamere = "^S2[A|B]_MSIL1C_.*_N.*_R.*_T.*_.*.zip$"
    for root,_,filenames in os.walk(sourcedir,topdown=True):
        for filename in filenames:
            if not re.match(zipnamere,filename): continue
            tile = filename.split('_')[5][1:]
            date = filename.split('_')[2][:6]
            data = select_table(cargs,tile)
            for qhmc, in data:
                sydir = os.path.join(targetdir,qhmc,date)
                if not os.path.exists(sydir): os.makedirs(sydir)
                tpath = os.path.join(sydir,filename)
                if os.path.exists(tpath): continue
                spath = os.path.join(root,filename)
                try:
                    os.link(spath, tpath)
                    print(f"Hard link created successfully from {spath} to {tpath}")
                except OSError as e:
                    print(f"Failed to create hard link: {e}")
    return


if __name__ == "__main__":
    cargs = {
        "database":"henan_rsanalysis",
        "user":"postgres",
        "password":"postgresql",
        "host":"192.168.1.71", 
        "port":"5432"}
    sourcedir = r"/data/ygjsb/sentinel2zip"
    targetdir = r"/data/ygjsb/ProvinceZip"
    main(sourcedir,targetdir,cargs)